/**
 * 
 */
package com.runningfox.logic;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

import com.runningfox.model.Joke;

/**
 * @author lhy
 * 
 */
public class DataBase {
	private static SQLiteDatabase db = null;
	static final String DB_NAME = "userdb";
	// static final String TABLE_HUMOR = "humor";
	static final String TABLE_STARJOKES = "starjokes";
	/**
	 * 笑话缓存 最多存储20条记录 新的替换最旧的
	 */
	static final int MAX_CACHE=20;
	static final String TABLE_ALLJOKES = "alljokes";
	
	static final String TABLE_JOKESDETAIL = "jokesdetail";

	public void saveFavouriteJoke(Joke node) {
		if (!isExist(TABLE_STARJOKES, String.valueOf(node.id))) {
			String sql = "INSERT INTO " + TABLE_STARJOKES
					+ " (hid,user,title,body,tag,url,read,star,timex) VALUES("
					+ "'" + node.id + "'," + "'" + node.user + "'," + "'"
					+ node.title + "'," + "'" + node.body + "'," + "'"
					+ node.tag + "'," + "'" + node.url + "'," + node.read + ","
					+ node.star + ",'" + node.dateStr + "')";
			Log.v("database", sql);
			db.execSQL(sql);
		}
	}

	/**
	 * 判断与id相等的记录是否存在
	 * 
	 * @param TABLE
	 * @param id
	 * @return
	 */
	public boolean isExist(String TABLE, String id) {
		String sqlExist = "SELECT hid FROM " + TABLE + " WHERE hid =" + id;
		Cursor cur = db.rawQuery(sqlExist, null);
		int cnt = cur.getCount();
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return cnt > 0 ? true : false;
	}

	public void updateOrInsertJokeNode(Joke node) {
		String hid = String.valueOf(node.id);
		// String sqlDetail = "UPDATE " + TABLE_JOKESDETAIL + " SET html='" +
		// node.html+"'"
		// + " WHERE hid=" + hid;
		String sqlAll = "UPDATE " + TABLE_ALLJOKES + " SET read=" + node.read
				+ ",like=" + node.like +",commentcnt= '"+node.comment +"' WHERE hid =" + hid;
		// if(!this.isExist(TABLE_JOKESDETAIL, hid)){
		// sqlDetail
		// ="INSERT INTO "+TABLE_JOKESDETAIL+"(hid,html)VALUES("+hid+",'"+node.html+"')";
		// }
		db.beginTransaction();
		// db.execSQL(sqlDetail);
		db.execSQL(sqlAll);
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public DataBase(Context ctx) {
		if (db == null) {
			DatabaseHelper dbHelper = new DatabaseHelper(ctx, DB_NAME, null, 1);
			db = dbHelper.getWritableDatabase();
		}
	}

	public SQLiteDatabase getConnection() {
		return db;
	}

	public void closeConnection() {
		db.close();
	}

	/**
	 * 获取更多笑话，
	 * 比hid小的。
	 * @param hid
	 * @return
	 */
	public ArrayList<Joke> getMoreJokeNodes(String hid) {
		ArrayList<Joke> list = new ArrayList<Joke>(10);
		String selectSql = "SELECT * from " + TABLE_ALLJOKES
				+ " WHERE hid<" + hid+ " ORDER BY timex DESC limit 2";
		Cursor cur = db.rawQuery(selectSql, null);
		int iHid = cur.getColumnIndex("hid");
		int iTitle = cur.getColumnIndex("title");
		int iUser = cur.getColumnIndex("user");
		int iBody = cur.getColumnIndex("body");
		int iTag = cur.getColumnIndex("tag");
		int iUrl = cur.getColumnIndex("url");
		int iRead = cur.getColumnIndex("read");
		int iStar = cur.getColumnIndex("star");
		int iDatetime = cur.getColumnIndex("timex");
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getMoreJokeNodes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.url = cur.getString(iUrl);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());

		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}

	/**
	 * start with page 0
	 * 
	 * @param page
	 * @param pagesize
	 * @return
	 */
	public ArrayList<Joke> getJokeNodesByPage(int page, int pagesize) {
		ArrayList<Joke> list = new ArrayList<Joke>(10);
		String selectSql = "SELECT * from " + TABLE_STARJOKES + " limit "
				+ page * pagesize + "," + pagesize + " orderby timex desc";
		Cursor cur = db.rawQuery(selectSql, null);
		int iHid = cur.getColumnIndex("hid");
		int iTitle = cur.getColumnIndex("title");
		int iUser = cur.getColumnIndex("user");
		int iBody = cur.getColumnIndex("body");
		int iTag = cur.getColumnIndex("tag");
		int iUrl = cur.getColumnIndex("url");
		int iRead = cur.getColumnIndex("read");
		int iStar = cur.getColumnIndex("star");
		int iDatetime = cur.getColumnIndex("timex");
		if (cur != null && cur.getCount() > 0) {
			do {
				Log.v("loop",  this.getClass().getName()+" getJokeNodesByPage");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.url = cur.getString(iUrl);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}

	/**
	 * 添加或者更新笑话缓存TABLE_ALLJOKES
	 * 
	 * @param node
	 */
	public void insertJokes(Joke node) {
		try {
			if (!this.isExist(TABLE_ALLJOKES, String.valueOf(node.id))) {
				String sql = "INSERT INTO "
						+ TABLE_ALLJOKES
						+ " (hid,user,title,body,tag,url,read,star,commentcnt,like,timex) VALUES("
						+  node.id + "," + "'" + node.user + "'," + "'"
						+ node.title + "'," + "'" + node.body + "'," + "'"
						+ node.tag + "'," + "'" + node.url + "'," + node.read
						+ "," + node.star + ","+node.comment+"," + node.like + ",'"
						+ node.dateStr + "')";
				db.execSQL(sql);
				while (this.getCnt(TABLE_ALLJOKES) > MAX_CACHE) {
					Log.v("loop",  this.getClass().getName()+" insertJokes");
					this.deleteOldest(TABLE_ALLJOKES);
				}
				log(sql);
			} else if (node.dirty == 1) {
				String sql = "UPDATE " + TABLE_ALLJOKES + " SET user='"
						+ node.user + "',body='" + node.body + "',title='"
						+ node.title + "',star="
						+ node.star + ",tag='" + node.tag + "',url='"
						+ node.url + "'," + "like=" + node.like +",commentcnt="+node.comment+ ",timex='"
						+ node.dateStr + "' WHERE hid =" + node.id;
				Log.v("database", sql);
				db.execSQL(sql);
				log(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	public ArrayList<Joke> getHotJokes(){
		String sql = "SELECT * FROM " + TABLE_ALLJOKES + " ORDER BY commentcnt DESC,like DESC";
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		ArrayList<Joke> list = new ArrayList<Joke>();
		if (cur != null && cur.getCount() > 0) {
			int iHid = cur.getColumnIndex("hid");
			int iTitle = cur.getColumnIndex("title");
			int iUser = cur.getColumnIndex("user");
			int iBody = cur.getColumnIndex("body");
			int iTag = cur.getColumnIndex("tag");
			int iUrl = cur.getColumnIndex("url");
			int iRead = cur.getColumnIndex("read");
			int iIndex = cur.getColumnIndex("like");
			int iComment =cur.getColumnIndex("commentcnt");
			int iStar = cur.getColumnIndex("star");
			int iDatetime = cur.getColumnIndex("timex");
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getAllJokes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.like = cur.getString(iIndex);
				n.comment =cur.getString(iComment);
				n.url = cur.getString(iUrl);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());

		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}
	
	/**
	 * 获取所有笑话
	 * 
	 * @return
	 */
	public ArrayList<Joke> getAllJokes() {
		String sql = "SELECT * FROM " + TABLE_ALLJOKES + " ORDER BY timex DESC";
//		String sql = "SELECT * FROM " + TABLE_ALLJOKES + " ORDER BY commentcnt DESC";
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		ArrayList<Joke> list = new ArrayList<Joke>();
		if (cur != null && cur.getCount() > 0) {
			int iHid = cur.getColumnIndex("hid");
			int iTitle = cur.getColumnIndex("title");
			int iUser = cur.getColumnIndex("user");
			int iBody = cur.getColumnIndex("body");
			int iTag = cur.getColumnIndex("tag");
			int iUrl = cur.getColumnIndex("url");
			int iRead = cur.getColumnIndex("read");
			int iIndex = cur.getColumnIndex("like");
			int iComment =cur.getColumnIndex("commentcnt");
			int iStar = cur.getColumnIndex("star");
			int iDatetime = cur.getColumnIndex("timex");
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getAllJokes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.like = cur.getString(iIndex);
				n.comment =cur.getString(iComment);
				n.url = cur.getString(iUrl);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());

		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}

	/**
	 * 获取加星的笑话
	 * 
	 * @return
	 */
	public ArrayList<Joke> getFavouriteJokes() {
		String sql = "SELECT * FROM " + TABLE_STARJOKES
				+ " ORDER BY timex DESC";
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		int iHid = cur.getColumnIndex("hid");
		int iTitle = cur.getColumnIndex("title");
		int iUser = cur.getColumnIndex("user");
		int iBody = cur.getColumnIndex("body");
		int iTag = cur.getColumnIndex("tag");
		int iUrl = cur.getColumnIndex("url");
		int iRead = cur.getColumnIndex("read");
		int iStar = cur.getColumnIndex("star");
		int iComment = cur.getColumnIndex("commentcnt");
		int iDatetime = cur.getColumnIndex("timex");
		ArrayList<Joke> list = new ArrayList<Joke>();
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getFavouriteJokes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.url = cur.getString(iUrl);
				n.comment =cur.getString(iComment);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());

		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}

	/**
	 * 删除最旧的一条记录
	 * 
	 * @param table
	 */
	public void deleteOldest(String table) {
		// select * from Table where time=(select max(time) from Table)
		String sql = "DELETE from " + table
				+ " WHERE timex =(SELECT min(timex) from " + table + ")";
		db.execSQL(sql);
		log(sql);
	}

	public String getOldest(String table){
		String sql ="SELECT min(timex) from "+table;
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		String date =null;
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
	 		date = cur.getString(0);
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return date;
	}
	
	void log(String sql) {
		Log.v("sql", sql);
	}

	/**
	 * 获取table的记录总数
	 * 
	 * @param table
	 * @return
	 */
	public int getCnt(String table) {
		// SELECT COUNT(DISTINCT store_name) FROM Store_Information
		String sql = "SELECT COUNT(hid) FROM " + table;
		log(sql);
		int cnt = -1;
		Cursor cur = db.rawQuery(sql, null);
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			cnt = cur.getInt(0);
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return cnt;
	}

	/**
	 * 获取已读的笑话
	 * 
	 * @return
	 */
	public ArrayList<Joke> getReadJokes() {
		String sql = "SELECT * FROM " + TABLE_ALLJOKES
				+ " WHERE read=1 ORDER BY timex DESC";
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		int iHid = cur.getColumnIndex("hid");
		int iTitle = cur.getColumnIndex("title");
		int iUser = cur.getColumnIndex("user");
		int iBody = cur.getColumnIndex("body");
		int iTag = cur.getColumnIndex("tag");
		int iUrl = cur.getColumnIndex("url");
		int iRead = cur.getColumnIndex("read");
		int iComment =cur.getColumnIndex("commentcnt");
		int iStar = cur.getColumnIndex("star");
		int iDatetime = cur.getColumnIndex("timex");
		ArrayList<Joke> list = new ArrayList<Joke>();
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getReadJokes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.url = cur.getString(iUrl);
				n.comment =cur.getString(iComment);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}
	
	
	/**
	 * 获取未读的笑话
	 * 
	 * @return
	 */
	public ArrayList<Joke> getUnreadJokes() {
		String sql = "SELECT * FROM " + TABLE_ALLJOKES
				+ " WHERE read=0 ORDER BY timex DESC";
		Cursor cur = db.rawQuery(sql, null);
		log(sql);
		int iHid = cur.getColumnIndex("hid");
		int iTitle = cur.getColumnIndex("title");
		int iUser = cur.getColumnIndex("user");
		int iBody = cur.getColumnIndex("body");
		int iTag = cur.getColumnIndex("tag");
		int iUrl = cur.getColumnIndex("url");
		int iRead = cur.getColumnIndex("read");
		int iComment =cur.getColumnIndex("commentcnt");
		int iStar = cur.getColumnIndex("star");
		int iDatetime = cur.getColumnIndex("timex");
		ArrayList<Joke> list = new ArrayList<Joke>();
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			do {
				Log.v("loop",  this.getClass().getName()+" getUnreadJokes");
				Joke n = new Joke();
				n.id = cur.getInt(iHid);
				n.body = cur.getString(iBody);
				n.tag = cur.getString(iTag);
				n.dateStr = cur.getString(iDatetime);
				n.star = cur.getInt(iStar);
				n.title = cur.getString(iTitle);
				n.user = cur.getString(iUser);
				n.url = cur.getString(iUrl);
				n.comment=cur.getString(iComment);
				n.read = cur.getInt(iRead);
				list.add(n);
			} while (cur.moveToNext());
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return list;
	}

	/**
	 * 更新笑话列表
	 * 
	 * @param list
	 */
	public void updateAllJokes(ArrayList<Joke> list) {
		int size = list.size();
		if (size > 0) {
			db.beginTransaction();
			// String sql = "DELETE FROM " + TABLE_ALLJOKES;
			// db.execSQL(sql);
			for (int i = 0; i < size; i++) {
				Log.v("loop",  this.getClass().getName()+" updateAllJokes");
				this.insertJokes(list.get(i));
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		}
	}

	/**
	 * 初始化数据库
	 * 
	 * @return
	 */
	public boolean initDatabase() {
		String createFavouriteTable = "CREATE TABLE IF NOT EXISTS "
				+ TABLE_STARJOKES + " (" + "hid int,"
				+ "title char(200)," + "user char(200)," + "body char(200),"
				+ "tag char(20)," + "url char(20)," + "read int," + "star int,"
				+"commentcnt int,"
				+ "timex datetime" + ")";
		String createAllTable = "CREATE TABLE IF NOT EXISTS " + TABLE_ALLJOKES
				+ " (" + "hid int," + "title char(200),"
				+ "user char(200)," + "body char(200)," + "tag char(20),"
				+ "url char(20)," + "read int," + "star int," + "like int,"
				+"commentcnt int,"
				+ "timex datetime" + ")";
		db.execSQL(createFavouriteTable);
		db.execSQL(createAllTable);
		return true;
	}

	/**
	 * 获取笑话内容
	 * 
	 * @param hid
	 * @return html文档
	 */
	public String getJokeHTML(String hid) {
		String sql = "SELECT * FROM " + TABLE_JOKESDETAIL + " WHERE hid=" + hid;
		Cursor cur = db.rawQuery(sql, null);
		String html = null;
		if (cur != null && cur.getCount() > 0) {
			cur.moveToFirst();
			int index = cur.getColumnIndex("html");
			html = cur.getString(index);
			cur.close();
			cur = null;
			return html;
		}
		if(cur!=null){
			cur.close();
		}
		cur = null;
		return html;
	}

	/**
	 * 加星笑话
	 * 
	 * @param node
	 * @return
	 */
	public boolean insertStarJoke(Joke node) {
		String insertJoke = "INSERT INTO " + TABLE_STARJOKES
				+ " (hid,user,title,body,tag,url,read,star,date) VALUES(" + node.id + "," + "'" + node.user + "'," + "'" + node.title
				+ "'," + "'" + node.body + "'," + "'" + node.tag + "'," + "'"
				+ node.url + "'," + node.read + "," + node.star + ",'"
				+ node.dateStr + "')";
		db.execSQL(insertJoke);
		return true;
	}

	public boolean saveAll(ArrayList<Joke> list) {
		int size = list.size();
		for (int i = 0; i < size; i++) {
			Log.v("loop",  this.getClass().getName()+" saveAll");
			this.insertStarJoke(list.get(i));
		}
		return true;
	}

	public void deleteFavouriteJoke(Joke node) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM " + TABLE_STARJOKES + " WHERE hid ="
				+ node.id;
		db.execSQL(sql);
	}
}

class DatabaseHelper extends SQLiteOpenHelper {

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {

	}

}
